package kr.ac.kongju.gedl.admin.web;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.StringReader;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;


import kr.ac.kongju.gedl.attach.service.AttachFileService;
import kr.ac.kongju.gedl.attach.vo.AttachFileVO;
import kr.ac.kongju.gedl.board.service.BoardContentsService;
import kr.ac.kongju.gedl.board.vo.ContentsVO;
import kr.ac.kongju.gedl.cmmn.web.CU;
import kr.ac.kongju.gedl.cmmn.web.NavigationController;
import kr.ac.kongju.gedl.code.vo.CodeInfoVO;
import kr.ac.kongju.gedl.common.util.ListResultVO;
import kr.ac.kongju.gedl.common.util.ResultCommand;
import kr.ac.kongju.gedl.item.service.ItemService;
import kr.ac.kongju.gedl.item.vo.ItemInfoVO;
import kr.ac.kongju.gedl.item.vo.ItemVO;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.dozer.DozerBeanMapperSingletonWrapper;
import org.hsqldb.lib.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.support.SessionStatus;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartRequest;
import org.springframework.web.servlet.ModelAndView;


import egovframework.rte.fdl.property.EgovPropertyService;
import egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationInfo;
import egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationManager;
import egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationRenderer;

@Controller
public class ItemAdminController {

	/** EgovPropertyService */
	@Resource(name = "propertiesService")
	protected EgovPropertyService propertiesService;

	@Resource(name = "itemService")
	protected ItemService itemService;
	
    @Resource(name = "attachFileService")
    protected AttachFileService attachFileService;

	public final Logger logger = LoggerFactory.getLogger(getClass());
	
	public static final String DEFAULT_AREA_CD = "R015";
	private final int PAGE_SIZE = 5;
    private final int ROW_PER_PAGE = 17;
    
    @Resource(name="paginationManager")
    protected PaginationManager paginationManager;
	
	
    @RequestMapping(value="/admin/itemList.do")
	public String selectItemListByArea(@ModelAttribute("searchVO") ItemVO searchVO, 
			ModelMap model, SessionStatus status, HttpSession session) throws Exception {
    	
    	searchVO.setPageSize(PAGE_SIZE);
    	searchVO.setRowPerPage(ROW_PER_PAGE);
    	
    	ListResultVO<ItemVO> resultList = itemService.retrieveItemList(searchVO);
		
    	String areaCd = searchVO.getAreaCd();
    	
    	if(StringUtils.isEmpty(areaCd)) {
    		areaCd = DEFAULT_AREA_CD;
    		searchVO.setAreaCd(areaCd);
    	}
    	
    	List<CodeInfoVO> regionList = CU.getInstance().getCodeList("REGION");
    	
    	
    	if(areaCd.length() >= 4) {
    		CodeInfoVO region = CU.getInstance().getCodeInfo(areaCd.substring(0, 4));
    		model.addAttribute("region", region);
    		
	    	model.addAttribute("regionList", CU.getInstance().getCodeList(areaCd.substring(0, 4)));
			
			CodeInfoVO subRegion = CU.getInstance().getCodeInfo(areaCd);
			model.addAttribute("subRegion", subRegion);
    	}
		
    	//페이징을 구성한다
    	PaginationInfo paginationInfo = new PaginationInfo();

		paginationInfo.setCurrentPageNo(searchVO.getPage());
		paginationInfo.setRecordCountPerPage(searchVO.getRowPerPage());
		paginationInfo.setPageSize(searchVO.getPageSize());

		paginationInfo.setTotalRecordCount(resultList.getListCount());
    	
		
    	
		model.addAttribute("searchVO", searchVO);
		model.addAttribute("regionList", regionList);
		model.addAttribute("resultList", resultList);
		model.addAttribute("paginationInfo", paginationInfo);
		
    	return "/admin/itemList";
	}


	@RequestMapping(value="/admin/itemUpload.do")
	public String upload(HttpServletRequest request, 
			ModelMap model, SessionStatus status) throws Exception {

		ItemVO itemVO = new ItemVO();

		List<CodeInfoVO> regionList1 = CU.getInstance().getCodeList("REGION");

		model.addAttribute("regionList1", regionList1);
		model.addAttribute("itemVO", itemVO);

		return "/admin/itemWrite";
	}
	
	@RequestMapping(value="/admin/itemForm.do")
	public String itemInfoWrite(@ModelAttribute("itemVO") ItemVO itemInfo,
			@RequestParam(value="viewType", defaultValue="insert") String viewType,
			ModelMap model, SessionStatus status) throws Exception {

		ItemVO retrieveItemVO = new ItemVO();
		
		if(viewType.equals("update") && itemInfo.getItemId().length() > 0) {
			retrieveItemVO = itemService.retrieveItemInfo(itemInfo);
			AttachFileVO attachFile = new AttachFileVO();
	    	attachFile.setContentsId(retrieveItemVO.getItemId());
	    	retrieveItemVO.setAttachFileList(attachFileService.selectListAttachFile(attachFile));
		}

		List<CodeInfoVO> regionList1 = CU.getInstance().getCodeList("REGION");
		
		List<CodeInfoVO> idxList = CU.getInstance().getCodeList("IDX");
		
		String areaCd = StringUtils.defaultIfEmpty(retrieveItemVO.getAreaCd(), "");
    	
    	if(areaCd.length() >= 4) {
	    	List<CodeInfoVO> regionList2 = CU.getInstance().getCodeList(areaCd.substring(0, 4));
			model.addAttribute("regionList2", regionList2);
    	}
		
    	if(areaCd.length() >= 7) {
    		List<CodeInfoVO> regionList3 = CU.getInstance().getCodeList(areaCd.substring(0, 7));
    		model.addAttribute("regionList3", regionList3);
    	}

    	model.addAttribute("idxList", idxList);
		model.addAttribute("regionList1", regionList1);
		model.addAttribute("itemVO", retrieveItemVO);

		return "/admin/itemForm";
	}
	
	@RequestMapping(value="/admin/insertItemInfo.do")
	public String insertItemInfo(@ModelAttribute("itemVO") ItemVO itemVO,
			HttpServletRequest request, ModelMap model, SessionStatus status) throws Exception {
		
		if (request instanceof MultipartHttpServletRequest) {
    		attachFileList(1, itemVO, request);
		}
		
		itemService.registerItemInfo(itemVO);
		
		return "redirect:/admin/itemForm.do?itemId="+itemVO.getItemId()+"&viewType=update";
	}
	
	@RequestMapping(value="/admin/updatetItemInfo.do")
	public String updatetItemInfo(@ModelAttribute("itemVO") ItemVO itemVO,
			HttpServletRequest request, ModelMap model, SessionStatus status) throws Exception {
		
		List<AttachFileVO> attachFileList = itemVO.getAttachFileList();
		AttachFileVO searchCondition = new AttachFileVO();
		searchCondition.setContentsId(itemVO.getItemId());
		List<AttachFileVO> oldAttachFileList = attachFileService.selectListAttachFile(searchCondition);
		
		itemService.updateItemInfo(itemVO);
		
		/**
		 * 기존 첨부파일목록과 화면에서 전달받은 첨부파일 목록을 비교하여 화면에서 받은 목록에
		 * 존재하지 않는 파일들은 삭제 처리 한다.
		 */
		for(AttachFileVO oldAttachFile : oldAttachFileList) {
			boolean isExist = false;
			for(AttachFileVO attachFile : attachFileList) {
				if(oldAttachFile.getAttachFileSeq() == attachFile.getAttachFileSeq()) {
					isExist = true;
				}
			}
			if(!isExist) {
				attachFileService.deleteAttachFile(oldAttachFile);
			}
		}
		// 첨부파일의 시퀀스는 기존 첨부파일 목록의 맨 마지막 시퀀스에 +1
		int attachFileSeq = 0;
		if(attachFileList != null && attachFileList.size() > 0) {
			attachFileSeq = attachFileList.get(attachFileList.size()-1).getAttachFileSeq() + 1;
		}
		if (request instanceof MultipartHttpServletRequest) {
			attachFileList(attachFileSeq, itemVO, request);
			
			if(itemVO.getAttachFileList() != null && itemVO.getAttachFileList().size() > 0) {
				attachFileService.registerAttachFileList(itemVO.getAttachFileList());
			}
    		
		}
		
		return "redirect:/admin/itemForm.do?itemId="+itemVO.getItemId()+"&viewType=update";
	}
	
	@RequestMapping(value="/admin/deletetItemInfo.do")
	public ModelAndView deletetItemInfo(@ModelAttribute("itemVO") ItemVO itemInfo,
			ModelMap model, SessionStatus status) throws Exception {
		
		ModelAndView modelAndView = new ModelAndView("jsonView");
		
		if(itemInfo.getItemId() != null && itemInfo.getItemId().length() > 0){	//등록
			int result = itemService.deleteItemInfo(itemInfo.getItemId());
			if(result > 0){
				modelAndView.addObject(ResultCommand.RESULT, "true");
	            modelAndView.addObject(ResultCommand.MESSAGE, "삭제되었습니다.");
	            
	            AttachFileVO attachFile = new AttachFileVO();
				attachFile.setContentsId(itemInfo.getItemId());
				attachFileService.deleteListAttachFile(attachFile);
				
			}else{
				modelAndView.addObject(ResultCommand.RESULT, "false");
	            modelAndView.addObject(ResultCommand.MESSAGE, "삭제시 오류가 발생하였습니다");
			}
		}else{											//갱신
			modelAndView.addObject(ResultCommand.RESULT, "false");
            modelAndView.addObject(ResultCommand.MESSAGE, "삭제시 오류가 발생하였습니다");
		}
		
		status.setComplete();
		
		return modelAndView;
	}
	
	

	@RequestMapping(value="/admin/uploadProcess.do", method = RequestMethod.POST)
	public String uploadProcess(HttpServletRequest request,ModelMap model, SessionStatus status) throws Exception {

		StringBuffer sbLog = new StringBuffer();
		if (request instanceof MultipartHttpServletRequest) {

			MultipartFile file = ((MultipartHttpServletRequest)request).getFile("excelFile");

			String fileName = file.getOriginalFilename();
			fileName = fileName.substring(0, 2);

			Workbook wb = WorkbookFactory.create(file.getInputStream());
			org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);

			int rowsCount = sheet.getLastRowNum();
			int idx = 1;

			String imageFilePath = propertiesService.getString("item.image.file.path");
			String musicFilePath = propertiesService.getString("item.music.file.path");
			String uploadFilePath = propertiesService.getString("upload.file.path");

			sbLog.append("Total Number of Rows: " + rowsCount+"<br/>");

			while (idx <= rowsCount) {
				ItemVO itemVO = new ItemVO();

				try {
					org.apache.poi.ss.usermodel.Row row = sheet.getRow(idx);
					int colCounts = row.getLastCellNum();
					System.out.println("Total Number of Cols: " + colCounts);
					if(colCounts > 0) {
						int j = 0; 
						String number = "";
						org.apache.poi.ss.usermodel.Cell cell = row.getCell(j++);
						if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
							number = cell.getStringCellValue(); // 연번
						} else {
							number = String.valueOf(cell.getNumericCellValue()); // 연번
						}

						if(!StringUtil.isEmpty(number)) {
							itemVO.setItemNm(row.getCell(j++).getStringCellValue()); //논매기소리
							itemVO.setOtherNm(row.getCell(j++).getStringCellValue()); // 다른 이름

							itemVO.setAreaIdxCd("");
							itemVO.setPplIdxCd("");
							itemVO.setNameIdxCd("");

							itemVO.setAreaNm(row.getCell(j++).getStringCellValue()); // 녹음 지역
							itemVO.setRecordDate(row.getCell(j++).getStringCellValue()); // 녹음날짜
							itemVO.setOriginTapeNm(row.getCell(j++).getStringCellValue()); // 원 녹음 테잎
							itemVO.setSingerNm(row.getCell(j++).getStringCellValue()); // 가창자
							itemVO.setSingerCareerHstr(row.getCell(j++).getStringCellValue()); // 가창자 이력
							itemVO.setRecorderNm(row.getCell(j++).getStringCellValue()); // 녹음자
							itemVO.setExtractorNm(row.getCell(j++).getStringCellValue()); // 채보자
							itemVO.setItemTypeCntn(row.getCell(j++).getStringCellValue()); // 유형분류
							itemVO.setSingingTypeCntn(row.getCell(j++).getStringCellValue()); // 가창방식
							itemVO.setPronunciationCntn(row.getCell(j++).getStringCellValue()); // 받음구 
							itemVO.setLeadCntn(row.getCell(j++).getStringCellValue()); // 메김구 예
							cell = row.getCell(j++);
							if(cell != null) {
								itemVO.setRefrainCntn(cell.getStringCellValue()); // 후렴 예
							}
							itemVO.setArticleCntn(row.getCell(j++).getStringCellValue()); // 본문 예
							itemVO.setEtcArticleCntn(row.getCell(j++).getStringCellValue()); // 기타 시설
							itemVO.setAdventNt(row.getCell(j++).getStringCellValue()); // 출현음
							itemVO.setCadenceNt(row.getCell(j++).getStringCellValue()); // 종지음
							itemVO.setFlexuralNt(row.getCell(j++).getStringCellValue()); // 요성음
							cell = row.getCell(j++);
							if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
								itemVO.setRhythmStrcCntn(cell.getStringCellValue()); // 리듬구조
							} else {
								itemVO.setRhythmStrcCntn(String.valueOf(cell.getNumericCellValue())); // 리듬구조
							}

							itemVO.setSongSpeedCntn(row.getCell(j++).getStringCellValue()); // 빠르기
							cell = row.getCell(j++);
							if(cell != null) {
							itemVO.setPublishAlbumNm(cell.getStringCellValue()); // 기발행 음반
							}
							itemVO.setReferenceBookNm(row.getCell(j++).getStringCellValue()); // 참고 서적

							String jpgFileName = row.getCell(j++).getStringCellValue();
							String mp3FileName = row.getCell(j++).getStringCellValue();

							itemVO.setSongExplanationCntn(row.getCell(j++).getStringCellValue());  //곡해설
							row.getCell(j++).getStringCellValue(); // 좌표(경)
							row.getCell(j++).getStringCellValue(); // 좌표(위)

							cell = row.getCell(j++);
							if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
								itemVO.setLongitude(cell.getStringCellValue()); // 좌표(경)
							} else {
								itemVO.setLongitude(String.valueOf(cell.getNumericCellValue())); // 좌표(경)
							}

							cell = row.getCell(j++);
							if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
								itemVO.setLatitude(cell.getStringCellValue()); // 좌표(위)
							} else {
								itemVO.setLatitude(String.valueOf(cell.getNumericCellValue())); // 좌표(위)
							}

							cell = row.getCell(j++);
							if(cell != null) {
								itemVO.setTmX(String.valueOf(cell.getNumericCellValue())); // TM_B_x
							}
							cell = row.getCell(j++);
							if(cell != null) {
								itemVO.setTmY(String.valueOf(cell.getNumericCellValue())); // TM_B_y
							}

							itemVO.setAreaCd(String.valueOf(row.getCell(j++).getStringCellValue()));

							List<AttachFileVO> attachFileList = new ArrayList<AttachFileVO>();
							if(!StringUtils.isEmpty(jpgFileName)  && jpgFileName.indexOf(".jpg") > 0) {
								AttachFileVO jpgFile = new AttachFileVO();
								String jpgSaveFileName = System.currentTimeMillis()+".jpg";
								jpgFile.setAttachFileSeq(1);
								jpgFile.setAttachFileTypeCd("01");
								jpgFile.setAttachFileNm(jpgSaveFileName); // 악보파일명
								jpgFile.setAttachFileOriginNm(jpgFileName);
								jpgFile.setAttachFilePath(imageFilePath+File.separatorChar+jpgSaveFileName);

								FileUtils.copyFile(new File(uploadFilePath+File.separatorChar+fileName+File.separatorChar+fileName+"_편집"+File.separatorChar+jpgFileName), new File(jpgFile.getAttachFilePath()));

								attachFileList.add(jpgFile);
							}

							if(!StringUtils.isEmpty(mp3FileName) && mp3FileName.indexOf(".mp3") > 0) {
								AttachFileVO mp3File = new AttachFileVO();

								String mp3SaveFileName = System.currentTimeMillis()+".mp3";
								mp3File.setAttachFileSeq(2);
								mp3File.setAttachFileTypeCd("02");
								mp3File.setAttachFileNm(mp3SaveFileName); // mp3파일명
								mp3File.setAttachFileOriginNm(mp3FileName);
								mp3File.setAttachFilePath(musicFilePath+File.separatorChar+mp3SaveFileName);
								FileUtils.copyFile(new File(uploadFilePath+File.separatorChar+fileName+File.separatorChar+fileName+" mp3"+File.separatorChar+mp3FileName), new File(mp3File.getAttachFilePath()));


								attachFileList.add(mp3File);
							}

							itemVO.setAttachFileList(attachFileList);

							itemService.registerItemInfo(itemVO);
						}
					}

				} catch(Exception ex) {
					sbLog.append("error row : "+idx+", message : "+ex.getMessage()+"<br/>");
					System.out.println("error: " + ex.toString());
				}
				idx++;

			}
			sbLog.append("Success rows : " +(idx-1));

		}

		ItemVO itemVO = new ItemVO();

		model.addAttribute("itemVO", itemVO);
		model.addAttribute("result", sbLog.toString());

		return "/admin/itemWrite";
	}
	
	public void attachFileList(int attachFileSeq, ItemVO itemVO, HttpServletRequest request) throws Exception{
    	List<AttachFileVO> attachFileList = new ArrayList<AttachFileVO>();
			
			// 파일 저장 경로
			String filePath = propertiesService.getString("attach.file.path")+File.separatorChar+DateFormatUtils.format(Calendar.getInstance(), "yyyyMMdd")+File.separatorChar;
			
			File attachDir = new File(filePath);
			if(!attachDir.exists()) {
				attachDir.mkdirs();
			}
			
			String contentsId = itemVO.getItemId();
			
			// 악보 파일
			MultipartFile attachfile = ((MultipartHttpServletRequest)request).getFile("jpgFile");
			if(!attachfile.isEmpty()) {
				attachFileList.add(processAttachFile(attachfile, filePath, contentsId, attachFileSeq, NavigationController.TYPE_IMAGE, "첨부파일"));
			}
			
			// 음원 파일
			attachfile = ((MultipartHttpServletRequest)request).getFile("mp3File");
			if(!attachfile.isEmpty()) {
				attachFileSeq++;
				attachFileList.add(processAttachFile(attachfile, filePath, contentsId, attachFileSeq, NavigationController.TYPE_MOVIE, "첨부파일"));
			}
			
			if(attachFileList.size() > 0) {
				itemVO.setAttachFileList(attachFileList);
			}
    }
	
	public static AttachFileVO processAttachFile(MultipartFile attachfile, String filePath, String contentsId, int attachFileSeq, String attachType, String fileDesc) throws Exception{
    	AttachFileVO attachFileVO = null;
    	String oName = attachfile.getOriginalFilename();
		String format = oName.substring(oName.lastIndexOf("."), oName.length());
		String newFileName = System.currentTimeMillis()+format;
		attachFileVO = new AttachFileVO();
		attachFileVO.setContentsId(contentsId);
		attachFileVO.setAttachFileSeq(attachFileSeq);
		attachFileVO.setAttachFileTypeCd(attachType);
		attachFileVO.setAttachFileNm(newFileName);
		attachFileVO.setAttachFileOriginNm(attachfile.getOriginalFilename());
		attachFileVO.setAttachFilePath(filePath+attachFileVO.getAttachFileNm());
		attachFileVO.setAttachFileDesc(fileDesc);
		FileOutputStream fos = new FileOutputStream(new File(attachFileVO.getAttachFilePath()));
		FileCopyUtils.copy(attachfile.getInputStream(), fos);
		fos.close();
		
		return attachFileVO;
    }

}
